library(rgdal)
library(ggplot2)
library(scales)
library(maptools)
library(gpclib)
library(gridExtra)
library(plyr)
library(MASS)

# Functions
sqLiteConnect <- function(database, table) {
  require(DBI)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

# Load meetup tables
meetup_user <-  sqLiteConnect("m5s_meetup_jun2015.sqlite", "member")
meetup_group <- sqLiteConnect("m5s_meetup_jun2015.sqlite", "[group]")
meetup_event <- sqLiteConnect("m5s_meetup_jun2015.sqlite", "event")

# Create Italy Map from shp
# DOWNLOAD shapefile FROM http://www.gadm.org/
italy_map <- readOGR(dsn = "/Users/francesco/Desktop/GIS_Data/Administrative units/Italy/ITA_adm/", layer = "ITA_adm1_simp", verbose=FALSE) 
italy_map@data$id = rownames(italy_map@data)
gpclibPermit() #avoid "Error: isTRUE(gpclibPermitStatus()) is not TRUE" in the next command
italy_map.points = fortify(italy_map,region="id")
italy_map.df = join(italy_map.points, italy_map@data, by="id")

# Snapshot dates
date1 = as.numeric(as.Date("2007-09-08"))
date2 = as.numeric(as.Date("2009-10-04"))
date3 = as.numeric(as.Date("2013-02-25"))
date4 = as.numeric(as.Date("2015-01-31"))
# date5 = as.numeric(as.Date("2014-01-01"))

# Maps
it_layer <- ggplot() + coord_equal()  +
  labs(x=NULL, y=NULL) + 
  theme(axis.line=element_blank(),
        axis.text.x=element_blank(),
        axis.text.y=element_blank(),
        axis.ticks=element_blank(),
        # axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.position="none",
        panel.background=element_rect(fill = 'white'),
        panel.border=element_blank(),
        panel.grid.major=element_blank(),
        panel.grid.minor=element_blank(),
        plot.background=element_blank(),
        plot.margin = unit(c(0,0,0,0), "cm"))

italy_box_y <- c(36, 47.5)
italy_box_x <- c(6.1, 19.5)  


# Groups
map1 <-  it_layer +
  # stat_density2d(data=subset(meetup_group, as.Date(created) < date1 & country == "IT"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
  geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
  geom_point(data=subset(meetup_group, as.Date(created) < date1 & country == "IT"), aes(x=lon, y=lat), colour="red", size=.7, alpha=.4) +
  coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) +
  xlab(format(as.Date(date1, origin = '1970-01-01'), format = "%e %b %Y"))
map2 <-  it_layer +
  # stat_density2d(data=subset(meetup_group, as.Date(created) < date2 & country == "IT"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
  geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
  geom_point(data=subset(meetup_group, as.Date(created) < date2 & country == "IT"), aes(x=lon, y=lat), colour="red", size=.7, alpha=.4) +
  coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) +
  xlab(format(as.Date(date2, origin = '1970-01-01'), format = "%e %b %Y"))
map3 <-  it_layer +
  # stat_density2d(data=subset(meetup_group, as.Date(created) < date3 & country == "IT"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
  geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
  geom_point(data=subset(meetup_group, as.Date(created) < date3 & country == "IT"), aes(x=lon, y=lat), colour="red", size=.7, alpha=.4) +
  coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) +
  xlab(format(as.Date(date3, origin = '1970-01-01'), format = "%e %b %Y"))
map4 <-  it_layer +
  # stat_density2d(data=subset(meetup_group, as.Date(created) < date4 & country == "IT"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
  geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
  geom_point(data=subset(meetup_group, as.Date(created) < date4 & country == "IT"), aes(x=lon, y=lat), colour="red", size=.7, alpha=.4) +
  coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) +
  xlab(format(as.Date(date4, origin = '1970-01-01'), format = "%e %b %Y"))
# map5 <-  it_layer +
#   # stat_density2d(data=subset(meetup_group, as.Date(created) < date5 & country == "IT"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
#   geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
#   geom_point(data=subset(meetup_group, as.Date(created) < date5 & country == "IT"), aes(x=lon, y=lat), colour="red", size=.7, alpha=.4) +
#   coord_cartesian(xlim = italy_box_x, ylim = italy_box_y )

# Users
# map6 <-  it_layer +
#   stat_density2d(data=subset(meetup_user, as.Date(joined) < date1 & country == "it"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
#   geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
#   geom_point(data=subset(meetup_user, as.Date(joined) < date1 & country == "it"), aes(x=lon, y=lat), colour="black", size=.4, alpha=.8) +
#   coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) + xlab("1 Jan 2006")
# map7 <-  it_layer +
#   stat_density2d(data=subset(meetup_user, as.Date(joined) < date2 & country == "it"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
#   geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
#   geom_point(data=subset(meetup_user, as.Date(joined) < date2 & country == "it"), aes(x=lon, y=lat), colour="black", size=.4, alpha=.7) +
#   coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) + xlab("1 Jan 2008")
# map8 <-  it_layer +
#   stat_density2d(data=subset(meetup_user, as.Date(joined) < date3 & country == "it"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
#   geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
#   geom_point(data=subset(meetup_user, as.Date(joined) < date3 & country == "it"), aes(x=lon, y=lat), colour="black", size=.4, alpha=.5) +
#   coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) + xlab("1 Jan 2010")
# map9 <-  it_layer +
#   stat_density2d(data=subset(meetup_user, as.Date(joined) < date4 & country == "it"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
#   geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
#   geom_point(data=subset(meetup_user, as.Date(joined) < date4 & country == "it"), aes(x=lon, y=lat), colour="black", size=.4, alpha=.2) +
#   coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) + xlab("1 Jan 2012")
# map10 <-  it_layer +
#   stat_density2d(data=subset(meetup_user, as.Date(joined) < date5 & country == "it"), aes(x=lon, y=lat, alpha=..level..), geom="polygon", fill="blue") +
#   geom_path(data=italy_map.df,aes(long,lat,group=group), colour="black", size=0.1) +
#   geom_point(data=subset(meetup_user, as.Date(joined) < date5 & country == "it"), aes(x=lon, y=lat), colour="black", size=.4, alpha=.1) +
#   coord_cartesian(xlim = italy_box_x, ylim = italy_box_y ) + xlab("1 Jan 2014")

grid.arrange(map1, map2, map3, map4, nrow=1)

# main = textGrob("The rise of Italy's Five Star Movement", gp=gpar(fontsize=24, fontfamily="Palatino")) 
# sub = textGrob("Groups (above) and Members (below). \nFrancesco Bailo, University of Sydney - Source: api.meetup.com", gp=gpar(fontsize=18, fontfamily="Palatino"))
# 
# grid.arrange(map1, map2, map3, map4, map5, 
#              map6, map7, map8, map9, map10,
#              ncol=5, nrow=2, main=main, sub=sub)
